package com.movie.repository;

import com.movie.entity.Movie;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface MovieRepository extends JpaRepository<Movie, Long> {
    
    Optional<Movie> findByTitle(String title);
    
    Page<Movie> findByStatus(String status, Pageable pageable);
    
    @Query("SELECT m FROM Movie m WHERE m.title LIKE %:keyword%")
    Page<Movie> findByTitleContaining(@Param("keyword") String keyword, Pageable pageable);
    
    @Query("SELECT m FROM Movie m WHERE m.category.id = :categoryId")
    Page<Movie> findByCategoryId(@Param("categoryId") Long categoryId, Pageable pageable);
    
    @Query("SELECT m FROM Movie m WHERE m.status = :status AND m.title LIKE %:keyword%")
    Page<Movie> findByStatusAndTitleContaining(@Param("status") String status, @Param("keyword") String keyword, Pageable pageable);
    
    @Query("SELECT m FROM Movie m WHERE m.rating >= :minRating ORDER BY m.rating DESC")
    List<Movie> findByRatingGreaterThanEqual(@Param("minRating") Double minRating, Pageable pageable);
    
    @Query("SELECT COUNT(m) FROM Movie m WHERE m.category.id = :categoryId")
    Long countByCategoryId(@Param("categoryId") Long categoryId);
    
    @Query("SELECT m FROM Movie m WHERE m.status = 'published' ORDER BY m.createdAt DESC")
    List<Movie> findLatestPublishedMovies(Pageable pageable);
    
    @Query("""
        SELECT m FROM Movie m 
        WHERE (:search IS NULL OR m.title LIKE %:search%)
        AND (:categoryId IS NULL OR m.category.id = :categoryId)
        AND (:status IS NULL OR m.status = :status)
        AND (:yearMin IS NULL OR m.year >= :yearMin)
        AND (:yearMax IS NULL OR m.year <= :yearMax)
        AND (:ratingMin IS NULL OR m.rating >= :ratingMin)
        AND (:ratingMax IS NULL OR m.rating <= :ratingMax)
        ORDER BY 
            CASE WHEN :sortBy = 'rating' AND :sortDir = 'desc' THEN m.rating END DESC,
            CASE WHEN :sortBy = 'rating' AND :sortDir = 'asc' THEN m.rating END ASC,
            CASE WHEN :sortBy = 'year' AND :sortDir = 'desc' THEN m.year END DESC,
            CASE WHEN :sortBy = 'year' AND :sortDir = 'asc' THEN m.year END ASC,
            CASE WHEN :sortBy = 'popularity' AND :sortDir = 'desc' THEN m.createdAt END DESC,
            CASE WHEN :sortBy = 'popularity' AND :sortDir = 'asc' THEN m.createdAt END ASC,
            m.id ASC
    """)
    Page<Movie> findMoviesWithFilters(
            @Param("search") String search,
            @Param("categoryId") Long categoryId,
            @Param("status") String status,
            @Param("yearMin") Integer yearMin,
            @Param("yearMax") Integer yearMax,
            @Param("ratingMin") Double ratingMin,
            @Param("ratingMax") Double ratingMax,
            @Param("sortBy") String sortBy,
            @Param("sortDir") String sortDir,
            Pageable pageable);
}